﻿Imports System.Data.OleDb
Public Class frmThongKeXuatKhoNoiBo
    Private ReportType As Integer = 0
    Private IndexCurrentMonth As Integer = 0
    Private bEdit As Boolean
    Private Sub frmThongKeNhapKho_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try
            Dim i As Integer
            For i = 0 To dgvThongKeNhap.ColumnCount - 1
                dgvThongKeNhap.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.EnableResizing
                dgvThongKeNhap.ColumnHeadersHeight = 21
                dgvThongKeNhap.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.BottomCenter
            Next
            KMergeHeaderDGV1.Grid = dgvThongKeNhap
            DisableControl()
            dtpTheoNgay.Enabled = True
            Nam_Load()
            Thang_Load()
            Report_TheoNgay()
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvThongKeNhap_Paint(ByVal sender As Object, ByVal e As System.Windows.Forms.PaintEventArgs) Handles dgvThongKeNhap.Paint
        KMergeHeaderDGV1.HeaderText = "Chứng từ xuất"
        KMergeHeaderDGV1.Merge(e, 0, 1)
        KMergeHeaderDGV1.HeaderText = "Chủng loại vật tư"
        KMergeHeaderDGV1.Merge(e, 2, 3)
        KMergeHeaderDGV1.HeaderText = FormatNumber(TongNhap, True)
        KMergeHeaderDGV1.Merge(e, 4, 4)
        KMergeHeaderDGV1.HeaderText = "Phần xuất vật tư"
        KMergeHeaderDGV1.Merge(e, 5, 5)
    End Sub
#Region "Thông kê nhập kho"
    Protected Function Create_NhapKho() As DataTable
        Dim dtTable As DataTable = New DataTable()
        Try
            Dim So As DataColumn = New DataColumn("So")
            So.DataType = System.Type.GetType("System.String")
            So.DefaultValue = ""
            dtTable.Columns.Add(So)

            Dim Ngay As DataColumn = New DataColumn("Ngay")
            Ngay.DataType = System.Type.GetType("System.String")
            Ngay.DefaultValue = ""
            dtTable.Columns.Add(Ngay)

            Dim MaSo As DataColumn = New DataColumn("MaSo")
            MaSo.DataType = System.Type.GetType("System.String")
            MaSo.DefaultValue = ""
            dtTable.Columns.Add(MaSo)

            Dim TenGoi As DataColumn = New DataColumn("TenGoi")
            TenGoi.DataType = System.Type.GetType("System.String")
            TenGoi.DefaultValue = ""
            dtTable.Columns.Add(TenGoi)

            Dim SoLuong As DataColumn = New DataColumn("SoLuong")
            SoLuong.DataType = System.Type.GetType("System.String")
            SoLuong.DefaultValue = ""
            dtTable.Columns.Add(SoLuong)

            Dim DienGiai As DataColumn = New DataColumn("DienGiai")
            DienGiai.DataType = System.Type.GetType("System.String")
            DienGiai.DefaultValue = ""
            dtTable.Columns.Add(DienGiai)
            Return dtTable
        Catch ex As Exception
            Throw
        End Try
    End Function
    Private TongNhap As Single = 0
    Public Sub Report_TheoNgay()
        Try
            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuXuatKhoNoiBo' And Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapPhieu='" & dtpTheoNgay.Value.ToShortDateString & "'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuXuatKhoNoiBo' And CT.LoaiPhieu=N'PhieuXuatKhoNoiBo' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapPhieu='" & dtpTheoNgay.Value.ToShortDateString & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "Ngay ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    detailRow = dtNhapKho.NewRow
                    detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
                    detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
                    detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
                    detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
                    SoLuong = 0
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
                        SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu"))
                    Next
                    detailRow.Item("SoLuong") = SoLuong
                    detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
                    dtNhapKho.Rows.Add(detailRow)
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            'dtFilterByProductCode.Columns.Add("So", Type.GetType("System.Integer"))
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                dr.Item("SoLuong") = dr.Item("SoLuong")
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
                'dr.Item("So") = Integer.Parse(dr.Item("MaSo"))
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoThang()
        Try
            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            Dim Thang As MonthInfo
            Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
            If Thang Is Nothing Then
                MessageBox.Show("Bạn chưa chọn tháng.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuXuatKhoNoiBo' And Xoa=0 And TrangThai=N'Đã duyệt' And ThangNhap='" & Thang.TenThang & "'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuXuatKhoNoiBo' And CT.LoaiPhieu=N'PhieuXuatKhoNoiBo' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.ThangNhap='" & Thang.TenThang & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham")
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    detailRow = dtNhapKho.NewRow
                    detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
                    detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
                    detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
                    detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
                    SoLuong = 0
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
                        SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu"))

                    Next
                    detailRow.Item("SoLuong") = SoLuong
                    detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
                    dtNhapKho.Rows.Add(detailRow)
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                dr.Item("SoLuong") = dr.Item("SoLuong")
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TheoNam()
        Try
            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            Dim Nam As YearInfo
            Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
            If Nam Is Nothing Then
                MessageBox.Show("Bạn chưa chọn năm.Vui lòng kiểm tra lại!", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuXuatKhoNoiBo' And Xoa=0 And TrangThai=N'Đã duyệt' And NamNhap='" & Nam.TenNam & "'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuXuatKhoNoiBo' And CT.LoaiPhieu=N'PhieuXuatKhoNoiBo' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NamNhap='" & Nam.TenNam & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    detailRow = dtNhapKho.NewRow
                    detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
                    detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
                    detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
                    detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
                    SoLuong = 0
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
                        SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu"))
                    Next
                    detailRow.Item("SoLuong") = SoLuong
                    detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
                    dtNhapKho.Rows.Add(detailRow)
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                dr.Item("SoLuong") = dr.Item("SoLuong")
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Public Sub Report_TuNgayDenNgay()
        Try
            Dim TuNgay As Integer = ConvertDateToInt(dtpTuNgay.Value.ToString.Split(" ")(0))
            Dim DenNgay As Integer = ConvertDateToInt(dtpDenNgay.Value.ToString.Split(" ")(0))
            If DenNgay < TuNgay Then
                Dim dlgRes As DialogResult
                dlgRes = MessageBox.Show( _
                          "Ngày đến không thể nhỏ hơn từ ngày.Vui lòng nhập lại", _
                          "Thông báo", _
                    MessageBoxButtons.OK, _
                          MessageBoxIcon.Question)
                If dlgRes = Windows.Forms.DialogResult.OK Then
                    Exit Sub
                End If
            End If

            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuXuatKhoNoiBo' And Xoa=0 And TrangThai=N'Đã duyệt' And NgayNhapTheoSo <=" & DenNgay & " And NgayNhapTheoSo >=" & TuNgay & ""
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuXuatKhoNoiBo' And CT.LoaiPhieu=N'PhieuXuatKhoNoiBo' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.NgayNhapTheoSo <=" & DenNgay & " And CT.NgayNhapTheoSo >=" & TuNgay & " And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    detailRow = dtNhapKho.NewRow
                    detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
                    detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
                    detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
                    detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
                    SoLuong = 0
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
                        SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu"))
                    Next
                    detailRow.Item("SoLuong") = SoLuong
                    detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
                    dtNhapKho.Rows.Add(detailRow)
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                'format number
                dr.Item("SoLuong") = dr.Item("SoLuong")
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            MessageBox.Show(ex.StackTrace)
        End Try
    End Sub
    Public Sub Report_TheoSoPhieu()
        Try

            TongNhap = 0
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dtIDSanPhamNhapKho As New DataTable
            Dim query As String
            query = "Select Distinct IDSanPham From ChiTietPhieuNhapKho Where LoaiPhieu=N'PhieuXuatKhoNoiBo' And Xoa=0 And TrangThai=N'Đã duyệt' And So=N'" & txtSoPhieu.Text.Trim & "'"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dtIDSanPhamNhapKho)
            Dim dtNhapKho As DataTable = Create_NhapKho()
            Dim SoLuong As Single = 0
            For i As Integer = 0 To dtIDSanPhamNhapKho.Rows.Count - 1
                query = "Select P.PhieuNhapKhoId,P.So,P.Ngay,P.NgayNhapBangSo,SP.MaSanPham,SP.TenSanPham,CT.SoLuongTheoCTu,P.NhapKhoTai" & _
                    " From PhieuNhapKho as P" & _
                    " Left Outer Join ChiTietPhieuNhapKho As CT On P.PhieuNhapKhoId=CT.PhieuNhapKhoId" & _
                    " Left Outer Join SanPham As SP On SP.IDSanPham=CT.IDSanPham" & _
                    " Where P.LoaiPhieu=N'PhieuXuatKhoNoiBo' And CT.LoaiPhieu=N'PhieuXuatKhoNoiBo' And P.Xoa=0 And CT.TrangThai=N'Đã duyệt' And CT.So=N'" & txtSoPhieu.Text.Trim & "' And CT.IDSanPham =" & dtIDSanPhamNhapKho.Rows(i)("IDSanPham") & " Order By P.So DESC"
                Dim dtSanPhamNhapKho As New DataTable
                adapter = New OleDb.OleDbDataAdapter(query, dbConn)
                adapter.Fill(dtSanPhamNhapKho)
                dtSanPhamNhapKho.DefaultView.Sort = "NgayNhapBangSo ASC"
                If dtSanPhamNhapKho.Rows.Count > 0 Then
                    Dim detailRow As DataRow
                    detailRow = dtNhapKho.NewRow
                    detailRow.Item("So") = dtSanPhamNhapKho.Rows(0)("So")
                    detailRow.Item("Ngay") = dtSanPhamNhapKho.Rows(0)("Ngay")
                    detailRow.Item("MaSo") = dtSanPhamNhapKho.Rows(0)("MaSanPham")
                    detailRow.Item("TenGoi") = dtSanPhamNhapKho.Rows(0)("TenSanPham")
                    SoLuong = 0
                    For j As Integer = 0 To dtSanPhamNhapKho.Rows.Count - 1
                        formatDate2VN(dtSanPhamNhapKho.Rows(j)("Ngay"))
                        SoLuong = SoLuong + Single.Parse(dtSanPhamNhapKho.Rows(j)("SoLuongTheoCTu"))
                    Next
                    detailRow.Item("SoLuong") = SoLuong
                    detailRow.Item("DienGiai") = dtSanPhamNhapKho.Rows(0)("NhapKhoTai")
                    dtNhapKho.Rows.Add(detailRow)
                End If
            Next
            dbConn.Close()
            'filter by code
            Dim dtFilterByProductCode As DataTable = dtNhapKho.Clone()
            If txtTenSanPham.Text <> "" Then
                For Each dr As DataRow In dtNhapKho.Rows
                    'check 
                    If dr.Item("MaSo") = txtTenSanPham.Text.Trim Then
                        dtFilterByProductCode.ImportRow(dr)
                    End If
                Next
            Else
                dtFilterByProductCode = dtNhapKho
            End If
            'cap nhat tong nhap
            For Each dr As DataRow In dtFilterByProductCode.Rows
                TongNhap = TongNhap + dr.Item("SoLuong")
                dr.Item("SoLuong") = dr.Item("SoLuong")
                dr.Item("Ngay") = Format(DateTime.Parse(dr.Item("Ngay")), "dd/MM/yyyy")
            Next
            Dim data As New DataTable
            Dim dv As DataView
            dv = dtFilterByProductCode.DefaultView
            dv.Sort = "Ngay DESC"
            data = dv.ToTable()

            dgvThongKeNhap.AutoGenerateColumns = False
            dgvThongKeNhap.DataSource = data
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
#Region "Filter Data"
    Sub DisableControl()
        dtpTheoNgay.Enabled = False
        cbxTheoThang.Enabled = False
        cbxNamTheoThang.Enabled = False
        cbxTheoNam.Enabled = False
        dtpTuNgay.Enabled = False
        dtpDenNgay.Enabled = False
        txtSoPhieu.Enabled = False
    End Sub

    Private Sub rbtTheoNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNgay.CheckedChanged
        DisableControl()
        dtpTheoNgay.Enabled = True
        ReportType = 0
    End Sub
    Private IsPostBack As Boolean = False
    Private Sub rbtTheoThang_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoThang.CheckedChanged
        DisableControl()
        cbxTheoThang.Enabled = True
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxTheoThang.SelectedIndex = IndexCurrentMonth
        End If
        cbxNamTheoThang.Enabled = True
        If IsPostBack = False Then
            cbxNamTheoThang.SelectedIndex = 0
        End If
        ReportType = 1
        IsPostBack = True
    End Sub
    Private IsPostBack2 As Boolean = False
    Private Sub rbtTheoNam_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoNam.CheckedChanged
        DisableControl()
        cbxTheoNam.Enabled = True
        If IsPostBack2 = False Then
            cbxTheoNam.SelectedIndex = 0
        End If
        ReportType = 2
        IsPostBack2 = True
    End Sub

    Private Sub rbtTuNgay_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTuNgay.CheckedChanged
        DisableControl()
        dtpTuNgay.Enabled = True
        dtpDenNgay.Enabled = True
        ReportType = 3
    End Sub
    Private Sub rbtTheoPhieu_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbtTheoPhieu.CheckedChanged
        Try
            DisableControl()
            txtSoPhieu.Enabled = True
            ReportType = 4
        Catch ex As Exception

        End Try
    End Sub
#End Region
#Region "Load combo tháng và combo năm"
#Region "Sub Class"
    Private Class YearInfo
        Dim _Nam As Integer
        Dim _TenNam As String
        Public Sub New(ByVal Nam As Integer, ByVal TenNam As String)
            _Nam = Nam
            _TenNam = TenNam
        End Sub
        Public Function Nam() As Integer
            Return _Nam
        End Function
        Public Function TenNam() As String
            Return _TenNam
        End Function
        Public Overrides Function ToString() As String
            Return _TenNam
        End Function
    End Class
    Private Class MonthInfo
        Dim _Thang As Integer
        Dim _TenThang As String
        Public Sub New(ByVal Thang As Integer, ByVal TenThang As String)
            _Thang = Thang
            _TenThang = TenThang
        End Sub
        Public Function Thang() As Integer
            Return _Thang
        End Function
        Public Function TenThang() As String
            Return _TenThang
        End Function
        Public Overrides Function ToString() As String
            Return _TenThang
        End Function
    End Class
#End Region
#Region "Load data"
    Private Sub Nam_Load()
        Dim currentYear As Integer = Now.Year
        cbxTheoNam.Items.Clear()
        Try
            While (currentYear >= 2011)
                cbxTheoNam.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                cbxNamTheoThang.Items.Add(New YearInfo(currentYear, currentYear.ToString))
                currentYear = currentYear - 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
    Private Sub Thang_Load()
        Dim currentYear As Integer = 1
        cbxTheoThang.Items.Clear()
        Try
            Dim i As Integer = 0
            While (currentYear <= 12)
                cbxTheoThang.Items.Add(New MonthInfo(currentYear, currentYear.ToString))
                If currentYear = Now.Month Then
                    IndexCurrentMonth = i
                End If
                currentYear = currentYear + 1
                i = i + 1
            End While
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region
#End Region
    Private Sub cmdThongKe_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdThongKe.Click
        Try
            Select Case ReportType
                Case 0
                    Report_TheoNgay()
                Case 1
                    Report_TheoThang()
                Case 2
                    Report_TheoNam()
                Case 3
                    Report_TuNgayDenNgay()
                Case 4
                    Report_TheoSoPhieu()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub cmdPrint_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdPrint.Click
        Try
            frmSaveFile.FileType = ".xls"
            ShowModalForm(frmSaveFile, False, False)
            Dim result As Boolean
            result = frmSaveFile.Result
            If result Then
                Dim Path As String = frmSaveFile.Path
                Dim FileName As String = frmSaveFile.FileName
                ExportExcel(Path, FileName & ".xls")
            End If
            frmSaveFile.txtPath.Text = ""
            frmSaveFile.txtFile.Text = ""
        Catch ex As Exception

        End Try
    End Sub
#Region "Export Excel"
    Private Sub ExportExcel(ByVal Path As String, ByVal FileName As String)
        Try
            Dim SubTitle As String
            If rbtTheoNgay.Checked Then
                SubTitle = "Ngày " & formatDateVN2String(dtpTheoNgay.Value)
            ElseIf rbtTheoThang.Checked Then
                Dim Thang As MonthInfo
                Thang = CType(cbxTheoThang.SelectedItem, MonthInfo)
                Dim Nam As YearInfo
                Nam = CType(cbxNamTheoThang.SelectedItem, YearInfo)
                SubTitle = " Tháng " & Thang.TenThang & " Năm " & Nam.TenNam
            ElseIf rbtTheoNam.Checked Then
                Dim Nam As YearInfo
                Nam = CType(cbxTheoNam.SelectedItem, YearInfo)
                SubTitle = " Năm " & Nam.TenNam
            ElseIf rbtTuNgay.Checked Then
                SubTitle = " Từ ngày " & formatDateVN2String(dtpTuNgay.Value.ToString.Split(" ")(0)) & " Đến ngày " & formatDateVN2String(dtpDenNgay.Value.ToString.Split(" ")(0))
            End If
            'write
            'read from template
            Dim CurrentDir As String = System.IO.Path.GetDirectoryName(Application.ExecutablePath)
            Dim PathTemPlate As String
            PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeXuatKhoNoiBo_Header.txt"
            Dim Header As String = String.Format(System.IO.File.ReadAllText(PathTemPlate), SubTitle, Format(TongNhap, "N02"))
            ExportToFile(Path, FileName, Header)
            'content
            Dim contentExcelXML As String = ""
            'first row
            If dgvThongKeNhap.Rows.Count > 0 Then
                contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                       "<Cell ss:Index=""" & "3""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(0, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(4, 0).Value & "</Data></Cell>" & vbCrLf & _
                       "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(5, 0).Value & "</Data></Cell>" & vbCrLf & _
                   "</Row>"
                ExportToFile(Path, FileName, contentExcelXML)
            End If
            'contentExcelXML = DevideRowGrid()
            Dim EndRow As Integer = dgvThongKeNhap.Rows.Count - 1
            Dim StartRow As Integer = 0
            Dim K As Integer = EndRow \ 100
            Dim SoDu As Integer = EndRow Mod 100
            Dim aK(100) As Integer
            For i As Integer = 1 To 100
                If K * i <= EndRow Then
                    aK(i) = K * i
                End If
            Next
            aK(100) = 100 * K + SoDu
            Dim sRet As String = ""
            frmBusyLoading.Show()
            frmBusyLoading.progress.Minimum = 0
            frmBusyLoading.progress.Maximum = 100
            For i As Integer = 0 To 99
                sRet = ConvertExcel(aK(i) + 1, aK(i + 1))
                If sRet <> "" Then
                    ExportToFile(Path, FileName, sRet)
                End If
                frmBusyLoading.progress.Value = i
            Next
            frmBusyLoading.Close()
            'end content
            PathTemPlate = CurrentDir + "\TemplateExcel\ThongKeXuatKhoNoiBo_End.txt"
            Dim Footer As String = System.IO.File.ReadAllText(PathTemPlate)
            ExportToFile(Path, FileName, Footer)
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        End Try
    End Sub
    Private Function ConvertExcel(ByVal iBegin As Integer, ByVal iEnd As Integer) As String
        Try
            Dim contentExcelXML As String = ""
            For i As Integer = iBegin To iEnd
                contentExcelXML = contentExcelXML & "<Row>" & vbCrLf & _
                    "<Cell ss:Index=""" & "3""" & "><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(0, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(1, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(2, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(3, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "Number" & """>" & dgvThongKeNhap(4, i).Value & "</Data></Cell>" & vbCrLf & _
                    "<Cell><Data ss:Type=""" & "String" & """>" & dgvThongKeNhap(5, i).Value & "</Data></Cell>" & vbCrLf & _
                "</Row>"
            Next
            Return contentExcelXML
        Catch ex As Exception

        End Try
    End Function
#End Region
#Region "Tìm kiếm sản phẩm"
    Private Sub SanPham_Search(ByVal KeyWord As String)
        Try
            Dim dbConn As New OleDb.OleDbConnection
            Dim dataAccess As New DataAccess
            Dim dsSanPham As New DataSet
            Dim dtSanPham As New DataTable
            Dim query As String = "SELECT * FROM (Select SanPham.DVTSanPhamId,IDSanPham,MaLoai,TenLoai,MaSanPham,TenSanPham,TenDonViTinh,DonViTinh.TenDonVi,SanPham.GhiChu,SanPham.Xoa From SanPham " & _
                                    " Left Outer Join LoaiSanPham On SanPham.IdLoai=LoaiSanPham.IdLoai " & _
                                    " Left Outer Join DVTSanPham On SanPham.DVTSanPhamId=DVTSanPham.DVTSanPhamId" & _
                                    " Left Outer Join DonViTinh On DonViTinh.IdDonVi=DVTSanPham.IDDonViTinh" & _
                                    " Where SanPham.Xoa=0 And SanPham.MaSanPham Like N'" & KeyWord & "%') AS SP WHERE SP.DVTSANPHAMID IS NOT NULL Order By TenSanPham"
            Dim adapter As OleDb.OleDbDataAdapter
            dataAccess.ConnectDatabase_DBAccess(dbConn)
            adapter = New OleDb.OleDbDataAdapter(query, dbConn)
            adapter.Fill(dsSanPham)
            dbConn.Close()
            dgvSanPham_Search.AutoGenerateColumns = False
            dtSanPham = dsSanPham.Tables(0)
            dtSanPham.Columns.Add("STT")
            For i As Integer = 0 To dtSanPham.Rows.Count - 1
                dtSanPham.Rows(i)("STT") = i + 1
                'chú giải
            Next

            dgvSanPham_Search.DataSource = dtSanPham
            If dgvSanPham_Search.Rows.Count - 1 > 0 Then
                dgvSanPham_Search.Rows(0).Selected = True
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub

    Private Sub txtTenSanPham_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyDown
        Try
            If e.KeyCode = Keys.Enter Then
                Dim TenSanPham As String
                Dim DVT As String
                TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                txtTenSanPham.Text = TenSanPham
                'hide search panel
                PanelSanPham_Search.Visible = False
                cmdThongKe.Focus()
            End If
        Catch ex As Exception

        End Try
    End Sub



    Private Sub txtTenSanPham_KeyUp(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtTenSanPham.KeyUp
        Try
            If txtTenSanPham.Text <> "" Then
                If e.KeyCode = Keys.Up Or e.KeyCode = Keys.Down Then
                    dgvSanPham_Search.Focus()
                End If
            End If
            txtTenSanPham.BackColor = Color.White
        Catch ex As Exception

        End Try
    End Sub
    Private Sub dgvSanPham_Search_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles dgvSanPham_Search.KeyDown
        Try
            Select Case e.KeyCode
                Case Keys.Down
                    PanelSanPham_Search.Visible = True
                Case Keys.Up
                    PanelSanPham_Search.Visible = True
                Case Keys.Enter
                    Dim TenSanPham As String
                    Dim DVT As String
                    TenSanPham = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("MaSanPham").Value.ToString
                    DVT = dgvSanPham_Search.Rows(dgvSanPham_Search.CurrentCell.RowIndex).Cells("TenDonViTinh").Value.ToString
                    txtTenSanPham.Text = TenSanPham
                    'hide search panel
                    PanelSanPham_Search.Visible = False
                    cmdThongKe.Focus()
                Case Else
                    txtTenSanPham.Focus()
            End Select
        Catch ex As Exception

        End Try
    End Sub

    Private Sub txtTenSanPham_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTenSanPham.TextChanged
        Try
            If txtTenSanPham.Text <> "" Then
                'show search panel
                If Not bEdit Then
                    PanelSanPham_Search.Visible = True
                    SanPham_Search(txtTenSanPham.Text)
                Else
                    PanelSanPham_Search.Visible = False
                End If
            Else
                'hide search panel
                PanelSanPham_Search.Visible = False
            End If
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
    Private Sub dgvSanPham_Search_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvSanPham_Search.CellDoubleClick
        Try
            'Gan Du Lieu
            Dim TenSanPham As String
            Dim DVT As String
            TenSanPham = dgvSanPham_Search.Rows(e.RowIndex).Cells("MaSanPham").Value.ToString
            DVT = dgvSanPham_Search.Rows(e.RowIndex).Cells("TenDonViTinh").Value.ToString
            txtTenSanPham.Text = TenSanPham
            'hide search panel
            PanelSanPham_Search.Visible = False
        Catch ex As Exception
            ShowError()
        End Try
    End Sub
#End Region
End Class